﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using huaguwang.Common;
using System.Collections;

namespace huaguwang
{
    public class D_T_AdminUser_Exp : D_T_AdminUser
    {        
        public M_T_AdminUser_Exp GetModel(string name, string pass)
        {
            pass = pass.Exp_Encrypt("201012071715");

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select top 1 ");
            strSql.Append(" a.id,a.loginName,a.RealName,a.PassWord,a.IsAdmin,c.id as powergroupid,c.powergroupname ");
            strSql.Append(" from t_adminuser a  ");
            strSql.Append(" inner join t_powergroupson b on a.id=b.userid ");
            strSql.Append(" inner join t_powergroup c on b.powergroupid=c.id ");
            strSql.Append(" where a.flag=1 ");
            strSql.Append(" and a.loginName=@name and a.PassWord=@pk ");
            var vConntionStr = huaguwang.MSSQLHelper.SQLConType.C1.GetConnectionString();
            var model = huaguwang.MSSQLHelper.SQLConType.C1.Exp_GetModel(
                    strSql.ToString(),
                    DR => DSToUserExp(DR),
                    new SqlParameter("@name", SqlDbType.NVarChar) { Value = name },
                    new SqlParameter("@pk", SqlDbType.NVarChar) { Value = pass });
            return model;
        }

        /// <summary>
        /// 判断菜单url是否重复
        /// </summary>
        public bool IsLogeName(string strURL, int id)
        {
            return MSSQLHelper.SQLConType.C1.Exp_ExistName(" T_AdminUser ",id, " loginName ", strURL, SqlDbType.NVarChar, "");
        }

        public int UpdateExp_1(M_T_AdminUser_Exp model)
        {


            var items = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                try
                {


                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("update T_AdminUser set ");
                    strSql.Append("loginName=@loginName,");
                    strSql.Append("RealName=@RealName,");
                    strSql.Append("IsAdmin=@IsAdmin ");
                    strSql.Append(" where id=@id ");
                    SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@loginName", SqlDbType.NVarChar,50),
					new SqlParameter("@RealName", SqlDbType.NVarChar,50),
					new SqlParameter("@PassWord", SqlDbType.NVarChar,50),
					new SqlParameter("@IsAdmin", SqlDbType.Bit,1),
					new SqlParameter("@Flag", SqlDbType.Bit,1)};
                    parameters[0].Value = model.id;
                    parameters[1].Value = model.LoginName;
                    parameters[2].Value = model.RealName;
                    parameters[3].Value = model.PassWord;
                    parameters[4].Value = model.IsAdmin;
                    parameters[5].Value = model.Flag;



                    com.Parameters.Add(parameters[0]);
                    com.Parameters.Add(parameters[1]);
                    com.Parameters.Add(parameters[2]);
                    com.Parameters.Add(parameters[3]);
                    com.Parameters.Add(parameters[4]);
                    com.Parameters.Add(parameters[5]);

                    com.CommandText = strSql.ToString();
                    int intID = com.ExecuteNonQuery();
                    com.Parameters.Clear();

                    if(0 >= intID)
                    {
                        tran.Rollback();
                        return -1;
                    }

                    strSql = new StringBuilder();

                    strSql.Append("update T_PowerGroupSon set ");
                    strSql.Append("PowerGroupID=@PowerGroupID,");
                    strSql.Append("UserID=@UserID,");
                    strSql.Append("Flag=Flag");
                    strSql.Append(" where UserID=@id ");
                    SqlParameter[] parameters1 = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@PowerGroupID", SqlDbType.Int,4),
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@Flag", SqlDbType.Bit,1)};
                    parameters1[0].Value = model.id;
                    parameters1[1].Value = model.PowerGroupID;
                    parameters1[2].Value = model.id;
                    parameters1[3].Value = model.Flag;

                    com.Parameters.Add(parameters1[0]);
                    com.Parameters.Add(parameters1[1]);
                    com.Parameters.Add(parameters1[2]);
                    com.Parameters.Add(parameters1[3]);
                    com.CommandText = strSql.ToString();
                    intID = com.ExecuteNonQuery();
                    com.Parameters.Clear();
                    if(0 >= intID)
                    {
                        tran.Rollback();
                        return -2;
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    return 0;
                }
                return model.id;
            });

            return items;
        }
        /// <summary>
        /// 插入用户资料,并且同时插入权限资料
        /// </summary>
        /// <param name="model">扩展用户</param>
        /// <returns>-2 权限插入错误,-1 用户插入错误, 0 出错,大于0 用户id</returns>
        public int AddExp_1(M_T_AdminUser_Exp model)
        {
            var items = MSSQLHelper.SQLConType.C1.ExecuteSqlTran((com, tran) =>
            {
                try
                {
                    //插入用户信息
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("insert into T_AdminUser(");
                    strSql.Append("loginName,RealName,PassWord,IsAdmin,Flag)");
                    strSql.Append(" values (");
                    strSql.Append("@loginName,@RealName,@PassWord,@IsAdmin,1)");
                    strSql.Append(";select @@IDENTITY");

                    SqlParameter[] parameters = {
					new SqlParameter("@loginName", SqlDbType.NVarChar,50),
					new SqlParameter("@RealName", SqlDbType.NVarChar,50),
					new SqlParameter("@PassWord", SqlDbType.NVarChar,50),
					new SqlParameter("@IsAdmin", SqlDbType.Bit,1),
					};
                    parameters[0].Value = model.LoginName;
                    parameters[1].Value = model.RealName;
                    parameters[2].Value = model.PassWord;
                    parameters[3].Value = model.IsAdmin;
                    com.Parameters.Add(parameters[0]);
                    com.Parameters.Add(parameters[1]);
                    com.Parameters.Add(parameters[2]);
                    com.Parameters.Add(parameters[3]);

                    com.CommandText = strSql.ToString();
                    int intID = com.ExecuteScalar().Exp_IntTryParse();
                    com.Parameters.Clear();

                    if(0 >= intID)
                    {
                        tran.Rollback();
                        return -1;
                    }
                    model.id = intID;

                    //插入权限信息
                    strSql = new StringBuilder();
                    strSql.Append("insert into T_PowerGroupSon(");
                    strSql.Append("PowerGroupID,UserID,Flag)");
                    strSql.Append(" values (");
                    strSql.Append("@PowerGroupID,@UserID,1)");
                    strSql.Append(";select @@IDENTITY");
                    SqlParameter[] parameters1 = {
					new SqlParameter("@PowerGroupID", SqlDbType.Int,4),
					new SqlParameter("@UserID", SqlDbType.Int,4),
					new SqlParameter("@Flag", SqlDbType.Bit,1)};
                    parameters1[0].Value = model.PowerGroupID;
                    parameters1[1].Value = intID;
                    parameters1[2].Value = true;
                    com.Parameters.Add(parameters1[0]);
                    com.Parameters.Add(parameters1[1]);
                    com.Parameters.Add(parameters1[2]);
                    com.CommandText = strSql.ToString();
                    intID = com.ExecuteScalar().Exp_IntTryParse();
                    com.Parameters.Clear();
                    if(0 >= intID)
                    {
                        tran.Rollback();
                        return -2;
                    }

                }
                catch
                {
                    tran.Rollback();
                    return 0;
                }
                tran.Commit();
                return model.id;

            });

            return items;
        }

        /// <summary>
        /// 重置密码
        /// </summary>
        public int UpdatePass(List<int> id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_AdminUser set ");
            strSql.Append("PassWord='" + "abc".Exp_Encrypt("201012071715") + "' ");
            strSql.Append(" where id in (" + id.Exp_JoinList(",", sel => sel.ToString()) + ") ");

            return  MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        public int UpdatePass(int id, string strOldPass, string NewPass)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_AdminUser set ");
            strSql.Append("PassWord='" + NewPass.Exp_Encrypt("201012071715") + "' ");
            strSql.Append(" where id=" + id + " and PassWord='" + strOldPass.Exp_Encrypt("201012071715") + "'");

            return MSSQLHelper.SQLConType.C1.Exp_ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int DelExp(int id)
        {
            return MSSQLHelper.SQLConType.C1.Exp_UpdateFlag(" T_AdminUser ",id);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int DelExp(List<int> id)
        {
            return MSSQLHelper.SQLConType.C1.Exp_UpdateFlag(" T_AdminUser ",id );
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsExp(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_AdminUser");
            strSql.Append(" where id=@id and flag=1  ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return  MSSQLHelper.SQLConType.C1.Exp_Exists(strSql.ToString(),parameters);
        }
        /// <summary>
        /// 判断有没有重复的登录名
        /// </summary>
        /// <param name="Name">名称</param>
        /// <param name="id">id</param>
        /// <returns>执行结果</returns>
        public bool IsURL(string Name, int id)
        {
            return MSSQLHelper.SQLConType.C1.Exp_ExistName("T_AdminUser",id, "loginName", Name, SqlDbType.NVarChar, "");
        }
        /// <summary>
        /// 把dr推导成一个类
        /// </summary>
        public M_T_AdminUser_Exp DSToUserExp(DataRow DR)
        {
            if(DR == null || DBNull.Value.Equals(DR))
            {
                return null;
            }
            return new M_T_AdminUser_Exp()
            {
                id = DR["id"].Exp_IntTryParse(),
                IsAdmin = DR["IsAdmin"].Exp_BoolTryParse(),
                LoginName = DR["loginName"].Exp_Trim(),
                PassWord = DR["PassWord"].Exp_Trim(),
                PowerGroupID = DR["powergroupid"].Exp_IntTryParse(),
                PowerGroupName = DR["powergroupname"].Exp_Trim(),
                RealName = DR["RealName"].Exp_Trim(),
            };

        }
        /// <summary>
        /// 把ds转换成菜单按钮
        /// </summary>
        /// <param name="DT"></param>
        /// <returns></returns>
        public M_T_MenuButton DSToMenuButton(DataTable DT)
        {
            if(DT == null || DBNull.Value.Equals(DT))
            {
                return null;
            }

            if(DT.Rows.Count > 0)
            {
                var model = new M_T_MenuButton();
                model.id = DT.Rows[0]["MenuID"].Exp_IntTryParse();
                model.MenuLevel = DT.Rows[0]["MenuLevel"].Exp_IntTryParse();
                model.MenuName = DT.Rows[0]["MenuName"].Exp_Trim();
                model.MenuParentID = DT.Rows[0]["MenuParentID"].Exp_IntTryParse();
                model.MenuParentList = DT.Rows[0]["MenuParentList"].Exp_Trim();
                model.MenuSonList = DT.Rows[0]["MenuSonList"].Exp_Trim();
                model.MenuURL = DT.Rows[0]["MenuURL"].Exp_Trim();
                model.Flag = DT.Rows[0]["flag"].Exp_BoolTryParse();
                model._ButtonList = new List<M_T_Button>();
                var List = new List<M_T_Button>();
                foreach(DataRow item in DT.Rows)
                {
                    List.Add(new M_T_Button()
                    {
                        ButtonName = item["ButtonName"].Exp_Trim(),
                        ButtonOrderBy = item["ButtonOrderBy"].Exp_IntTryParse(),
                        Flag = item["flag"].Exp_BoolTryParse(),
                        id = item["id"].Exp_IntTryParse(),
                        MenuID = item["MenuID"].Exp_IntTryParse()

                    });
                }
                model._ButtonList = List.OrderBy(Sel => Sel.ButtonOrderBy).ToList();
                return model;
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 获得一个登录时候的实体资料
        /// </summary>
        public M_T_AdminUser_Exp GetModelExp(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select top 1 ");
            strSql.Append(" a.id,a.loginName,a.RealName,a.PassWord,a.IsAdmin,c.id as powergroupid,c.powergroupname ");
            strSql.Append(" from t_adminuser a  ");
            strSql.Append(" inner join t_powergroupson b on a.id=b.userid ");
            strSql.Append(" inner join t_powergroup c on b.powergroupid=c.id ");
            strSql.Append(" where a.flag=1 ");
            strSql.Append(" and a.id=@id ");
            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel(
                    strSql.ToString(),
                    DR => DSToUserExp(DR),
                    new SqlParameter("@id", SqlDbType.Int) { Value = id });

            return model;
        }
        /// <summary>
        /// 获得一个登录时候的实体资料
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public M_T_AdminUser_Exp GetLoginUser(string strName)
        {
            strName = strName.Exp_Trim();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select top 1 ");
            strSql.Append(" a.id,a.loginName,a.RealName,a.PassWord,a.IsAdmin,c.id as powergroupid,c.powergroupname ");
            strSql.Append(" from t_adminuser a  ");
            strSql.Append(" inner join t_powergroupson b on a.id=b.userid ");
            strSql.Append(" inner join t_powergroup c on b.powergroupid=c.id ");
            strSql.Append(" where a.flag=1 ");
            strSql.Append(" and a.login=@lognName ");
            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel(
                    strSql.ToString(),
                    DR => DSToUserExp(DR),
                    new SqlParameter("@lognName", SqlDbType.NVarChar, 30) { Value = strName });

            return model;
        }
        /// <summary>
        /// 获得一个登录时候的实体资料
        /// </summary>
        /// <param name="strName"></param>
        /// <returns></returns>
        public M_T_AdminUser_Exp GetLoginUser(string strName, string PassWrod)
        {
            strName = strName.Exp_Trim();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select top 1 ");
            strSql.Append(" a.id,a.loginName,a.RealName,a.PassWord,a.IsAdmin,c.id as powergroupid,c.powergroupname ");
            strSql.Append(" from t_adminuser a  ");
            strSql.Append(" inner join t_powergroupson b on a.id=b.userid ");
            strSql.Append(" inner join t_powergroup c on b.powergroupid=c.id ");
            strSql.Append(" where a.flag=1 ");
            strSql.Append(" and a.loginname=@lognName and a.PassWord=@PassWord");
            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel(
                    strSql.ToString(),
                    DR => DSToUserExp(DR),
                    new SqlParameter("@lognName", SqlDbType.NVarChar, 30) { Value = strName },
                    new SqlParameter("@PassWord", SqlDbType.NVarChar, 50) { Value = PassWrod }
                    );

            return model;
        }

        /// <summary>
        /// 创建一个通用的获取按钮菜单的sql语句
        /// </summary>
        /// <returns></returns>
        public StringBuilder GetMenuButtonUserSQL(int intTop)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT   ");
            if(0 >= intTop)
            {
                strSql.Append(" top " + intTop);
            }
            strSql.Append("  e.id, e.ButtonName, e.ButtonOrderBy, e.MenuID,e.IsLeft,f.MenuName, ");
            strSql.Append(" f.MenuURL, f.MenuParentID, f.MenuLevel, f.MenuParentList, f.MenuSonList ");
            strSql.Append(" FROM         dbo.T_AdminUser AS a INNER JOIN ");
            strSql.Append(" dbo.T_PowerGroupSon AS b ON a.id = b.UserID INNER JOIN ");
            strSql.Append(" dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id INNER JOIN ");
            strSql.Append(" dbo.T_Power AS d ON d.PowerGroupID = c.id INNER JOIN ");
            strSql.Append(" dbo.T_Button AS e ON d.ButtonID = e.id INNER JOIN ");
            strSql.Append(" dbo.T_Menu AS f ON e.MenuID = f.id ");
            strSql.Append(" where a.flag=1 and b.flag=1 and c.flag=1 and e.flag=1 and f.flag=1 ");
            return strSql;
        }
        /// <summary>
        /// 创建一个通用的获取按钮菜单的sql语句
        /// </summary>
        /// <returns></returns>
        public StringBuilder GetMenuButtonSQL(int intTop)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT   ");
            if(0 >= intTop)
            {
                strSql.Append(" top " + intTop);
            }
            strSql.Append("  e.id, e.ButtonName, e.ButtonOrderBy, e.MenuID,e.IsLeft,f.MenuName, ");
            strSql.Append(" f.MenuURL, f.MenuParentID, f.MenuLevel, f.MenuParentList, f.MenuSonList ");
            strSql.Append(" FROM          ");
            strSql.Append(" dbo.T_PowerGroupSon AS b  INNER JOIN ");
            strSql.Append(" dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id INNER JOIN ");
            strSql.Append(" dbo.T_Power AS d ON d.PowerGroupID = c.id INNER JOIN ");
            strSql.Append(" dbo.T_Button AS e ON d.ButtonID = e.id INNER JOIN ");
            strSql.Append(" dbo.T_Menu AS f ON e.MenuID = f.id ");
            strSql.Append(" where  b.flag=1 and c.flag=1 and e.flag=1 and f.flag=1 ");
            return strSql;
        }

        /// <summary>
        /// 创建一个通用的获取菜单资料的sql语句
        /// </summary>
        /// <param name="strWhere">额外条件</param>
        /// <returns></returns>
        public StringBuilder GetMenuListSQL(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT DISTINCT  g.MenuName AS PMenuName, g.id AS PID, f.id, f.MenuName, f.MenuURL ");
            strSql.Append("  FROM dbo.T_AdminUser AS a INNER JOIN ");
            strSql.Append("  dbo.T_PowerGroupSon AS b ON a.id = b.UserID INNER JOIN ");
            strSql.Append("  dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id INNER JOIN ");
            strSql.Append("  dbo.T_Power AS d ON d.PowerGroupID = c.id INNER JOIN ");
            strSql.Append("  dbo.T_Button AS e ON d.ButtonID = e.id INNER JOIN ");
            strSql.Append("  dbo.T_Menu AS f ON e.MenuID = f.id INNER JOIN ");
            strSql.Append("  dbo.T_Menu AS g ON g.id = f.MenuParentID ");
            strSql.Append("  WHERE     (b.Flag = 1) AND (c.Flag = 1) AND (e.Flag = 1) AND (f.Flag = 1) AND (a.Flag = 1) ");
            strSql.Append(strWhere);
            strSql.Append("  ORDER BY PID, f.id ");

            return strSql;
        }
        public StringBuilder GetMenuListSQLAdmin(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT DISTINCT  g.MenuName AS PMenuName, g.id AS PID, f.id, f.MenuName, f.MenuURL ");
            strSql.Append("  FROM  ");
            strSql.Append("  dbo.T_Menu AS f   INNER JOIN ");
            strSql.Append("  dbo.T_Menu AS g ON g.id = f.MenuParentID ");
            strSql.Append("  WHERE      (f.Flag = 1)  ");
            strSql.Append(strWhere);
            strSql.Append("  ORDER BY PID, f.id ");

            return strSql;
        }
        /// <summary>
        /// 根据用户id查询得到相关资料
        /// </summary>
        public List<M_T_MenuExp> GetMenuList()
        {
            var strSql = GetMenuListSQLAdmin("");
            var list = MSSQLHelper.SQLConType.C1.Exp_GetList(strSql.ToString(),
                DR => new M_T_MenuExp()
                {
                    PID = DR["PID"].Exp_IntTryParse(),
                    PName = DR["PMenuName"].Exp_Trim(),
                    id = DR["id"].Exp_IntTryParse(),
                    MenuURL = DR["MenuURL"].Exp_Trim(),
                    MenuName = DR["MenuName"].Exp_Trim(),
                });

            return list;

        }
        /// <summary>
        /// 根据用户id查询得到相关资料
        /// </summary>
        public List<M_T_MenuExp> GetMenuList(int strUID)
        {
            var strSql = GetMenuListSQL(" and a.id=@id");
            var list = MSSQLHelper.SQLConType.C1.Exp_GetList(strSql.ToString(),
                DR => new M_T_MenuExp()
                {
                    PID = DR["PID"].Exp_IntTryParse(),
                    PName = DR["PMenuName"].Exp_Trim(),
                    id = DR["id"].Exp_IntTryParse(),
                    MenuURL = DR["MenuURL"].Exp_Trim(),
                    MenuName = DR["MenuName"].Exp_Trim(),
                },
                new SqlParameter("@id", SqlDbType.Int) { Value = strUID });

            return list;

        }
        /// <summary>
        /// 根据url和用户登录名获得相关页面的权限
        /// </summary>
        /// <param name="strURL"></param>
        /// <param name="strName"></param>
        /// <returns></returns>
        public M_T_MenuButton GetButton(string strURL, string strName, bool isAdmin)
        {
            StringBuilder strSql;
            if(isAdmin)
            {
                strSql = GetMenuButtonSQL(1);
                strSql.Append(" and  f.Menuurl=@url    ");
            }
            else
            {
                strSql = GetMenuButtonUserSQL(1);
                strSql.Append(" and  f.Menuurl=@url and a.loginname=@name  ");
            }

            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel(
                   strSql.ToString(),
                   DR => DSToMenuButton(DR),
                   new SqlParameter("@url", SqlDbType.NVarChar, 100) { Value = strURL },
                   new SqlParameter("@name", SqlDbType.NVarChar, 50) { Value = strName }
                   );
            return model; ;
        }
        /// <summary>
        /// 根据url和用户登录名获得相关页面的权限
        /// </summary>
        /// <param name="strURL"></param>
        /// <param name="strName"></param>
        /// <returns></returns>
        //public M_T_MenuButton GetButton(string strURL, int userID, bool isAdmin)
        //{
        //    StringBuilder strSql;
        //    if (isAdmin)
        //    {
        //        strSql = GetMenuButtonSQL(1);
        //        strSql.Append(" and  f.Menuurl=@url ");
        //    }
        //    else
        //    {
        //        strSql = GetMenuButtonUserSQL(1);
        //        strSql.Append(" and  f.Menuurl=@url and a.id=@id");
        //    }


        //    var model = strSql.ToString().Exp_GetModel(
        //           YXSQL.SQLConType.C1,
        //           DR => DSToMenuButton(DR),
        //           new SqlParameter("@url", SqlDbType.NVarChar, 100) { Value = strURL },
        //           new SqlParameter("@id", SqlDbType.Int) { Value = userID }
        //           );
        //    return model; ;
        //}
        public M_T_MenuButton GetButton(string strURL, int userID, bool isAdmin)
        {
            StringBuilder strSql = new StringBuilder();
            if(!isAdmin)
            {
                strSql.Append(" select a.*,flag=cast(case when b.id is null then 0 else 1 end as bit)  from ( ");
                strSql.Append(" select b.id, b.ButtonName, b.ButtonOrderBy, b.MenuID,a.MenuName,  ");
                strSql.Append(" a.MenuURL, a.MenuParentID, a.MenuLevel, a.MenuParentList, a.MenuSonList  from  ");
                strSql.Append(" t_menu a inner join t_button b on a.id=b.menuid  ");
                strSql.Append(" where a.menuUrl=@url and a.flag=1 and b.flag=1 ");
                strSql.Append(" ) a ");
                strSql.Append(" left join ( ");
                strSql.Append(" select e.id from ");
                strSql.Append(" dbo.T_PowerGroupSon AS b  INNER JOIN  ");
                strSql.Append(" dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id INNER JOIN  ");
                strSql.Append(" dbo.T_Power AS d ON d.PowerGroupID = c.id INNER JOIN  ");
                strSql.Append(" dbo.T_Button AS e ON d.ButtonID = e.id INNER JOIN  ");
                strSql.Append(" dbo.T_Menu AS f ON e.MenuID = f.id  ");
                strSql.Append(" where f.menuUrl=@url and b.flag=1 and c.flag=1 and e.flag=1 and f.flag=1  ");
                strSql.Append(" and b.userid=@userid and d.flag=1 ");
                strSql.Append(" ) b ");
                strSql.Append(" on a.id=b.id order by  ButtonOrderBy ");
            }
            else
            {

                strSql.Append("   ");
                strSql.Append(" select b.id, b.ButtonName, b.ButtonOrderBy, b.MenuID,a.MenuName,flag=cast(1 as bit) , ");
                strSql.Append(" a.MenuURL, a.MenuParentID, a.MenuLevel, a.MenuParentList, a.MenuSonList  from  ");
                strSql.Append(" t_menu a inner join t_button b on a.id=b.menuid  ");
                strSql.Append(" where a.menuUrl=@url and a.flag=1 and b.flag=1 order by  ButtonOrderBy  ");
                strSql.Append("  ");

            }

            var model = MSSQLHelper.SQLConType.C1.Exp_GetModel(strSql.ToString(),
                DR => DSToMenuButton(DR),
                new SqlParameter("@url", SqlDbType.NVarChar, 200) { Value = strURL },
                new SqlParameter("@userid", SqlDbType.Int, 200) { Value = userID }
                );
            return model;


        }

        public List<M_T_AdminUser_Exp> GetListPage(int b, int p, string GroupName, string Name)
        {
            //SELECT     a.id, a.loginName, a.RealName, a.IsAdmin, c.PowerGroupName
            //FROM         dbo.T_AdminUser AS a INNER JOIN
            //                      dbo.T_PowerGroupSon AS b ON a.id = b.UserID INNER JOIN
            //                      dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id

            var strSql = ExpandMSSQL.GetPageSQL(b, p,
                "  a.id, a.loginName, a.RealName, a.IsAdmin, c.PowerGroupName ",
                "   dbo.T_AdminUser AS a INNER JOIN dbo.T_PowerGroupSon AS b ON a.id = b.UserID INNER JOIN dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id ",
                " a.id ",
                " a.flag=1 and a.loginName like '%'+@Name+'%' and  c.powergroupname like '%'+@GName+'%'",
                " a.id desc  ");
            var list = huaguwang.MSSQLHelper.SQLConType.C1.Exp_GetList(strSql,
                    DR => new M_T_AdminUser_Exp()
                    {
                        PowerGroupName = DR["powergroupname"].Exp_Trim(),
                        Flag = true,
                        id = DR["id"].Exp_IntTryParse(),
                        IsAdmin = DR["IsAdmin"].Exp_BoolTryParse(),
                        LoginName = DR["loginName"].Exp_Trim(),
                        RealName = DR["RealName"].Exp_Trim(),
                    },
                    new SqlParameter("@GName", SqlDbType.NVarChar, 30) { Value = GroupName },
                    new SqlParameter("@Name", SqlDbType.NVarChar, 30) { Value = Name });

            return list;

        }
        public int GetListPage(string GroupName, string Name)
        {
            var strSql = ExpandMSSQL.GetPageSQL(
                "   dbo.T_AdminUser AS a INNER JOIN dbo.T_PowerGroupSon AS b ON a.id = b.UserID INNER JOIN dbo.T_PowerGroup AS c ON b.PowerGroupID = c.id ",
                " a.flag=1 and a.loginName like '%'+@Name+'%' and  c.powergroupname like '%'+@GName+'%'"
                );
            var intRef = MSSQLHelper.SQLConType.C1.Exp_GetSingle(strSql,
                    new SqlParameter("@GName", SqlDbType.NVarChar, 30) { Value = GroupName },
                    new SqlParameter("@Name", SqlDbType.NVarChar, 30) { Value = Name });
            return intRef.Exp_IntTryParse();
        }

    }
}